import pymysql
from cassandra.policies import HostFilterPolicy, RoundRobinPolicy
from cassandra.cluster import Cluster
from cassandra.auth import PlainTextAuthProvider

CASSANDRA_USER = 'beaconuser1'
CASSANDRA_PWD = 'dbs123'
CASSANDRA_DB = 'device_data_st'
CASSANDRA_HOST = ['10.167.13.9', '10.167.13.16', '10.167.13.11', '10.167.13.12', '10.167.13.13']


def cassandra_connect():
    def address_is_ignored(host):
        return host.address in CASSANDRA_HOST

    blacklist_filter_policy = HostFilterPolicy(
        child_policy=RoundRobinPolicy(),
        predicate=address_is_ignored
    )

    cluster = Cluster(CASSANDRA_HOST,
                      auth_provider=PlainTextAuthProvider(username=CASSANDRA_USER, password=CASSANDRA_PWD),
                      load_balancing_policy=blacklist_filter_policy)

    conn = cluster.connect(CASSANDRA_DB)
    return conn


class Database:
    def __init__(self, host, dbname):
        self.host = host
        self.port = 3306
        self.user = 'ims'
        # self.user = 'user1'
        self.passwd = 'Ims123456!'
        # self.passwd = 'Beaconuser1!'
        self.db = dbname
        self.dbtype = 'mysql'
        self.conn = None
        self.cur = None
        self.charset = 'utf8'

    def get_conn(self):
        if self.dbtype == 'mysql':
            return pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db,
                                   charset=self.charset)
        else:
            return False

    def get_cursor(self):
        try:
            self.conn = self.get_conn()
            self.cur = self.conn.cursor()
            return True
        except Exception as err:
            print('db %s failed,Message:%s' % (self.dbtype, err))
            return False

    def close(self):
        self.cur.close()
        self.conn.close()

    def select_table(self, sql):
        try:
            self.cur.execute(sql)
            rows = self.cur.fetchall()
            return rows
        except Exception as err:
            print('Message:%s' % (err))

    def insert_table(self, sql):
        try:
            self.cur.execute(sql)
            a = self.conn.commit()
            return a
        except Exception as err:
            print('Message:%s' % err)


def my_select(sql):
    db = Database(host='10.129.4.131', dbname="db_st_ims")
    # db = Database(host='10.129.7.200', dbname="db_st_ims")
    db.get_conn()
    db.get_cursor()
    data = db.select_table(sql)
    db.close()
    return data


def my_select1(sql):
    db = Database(host='10.129.4.131', dbname="db_st_ims")
    # db = Database(host='10.129.7.200', dbname="db_st_ims")
    db.get_conn()
    db.get_cursor()
    data = db.insert_table(sql)
    db.close()
    return data


def my_select2(sql):
    db = Database(host='10.129.7.200', dbname="db_productiondata")
    db.get_conn()
    db.get_cursor()
    data = db.select_table(sql)
    db.close()
    return data


SQL = {
    "img_line_sections_name_addr": "select img_url,line,sections,new_name,gw_name,ip_address from view_total WHERE site='{}' ORDER  BY sections,line",
    "by_machine_get_t_name": "select t_name,op_time from tb_productionmessage WHERE m_no='{}' ORDER BY id desc limit 1",
    "get_d_code": "select t_name,op_time from tb_productionmessage WHERE m_no='{}' ORDER BY id desc limit 1",
    "get_t_name_by_m_no": "select t_name from tb_productionmessage WHERE m_no={} ORDER BY id DESC limit 1",
    "get_ct_op_time": "select b.ct,a.op_time from tb_productionmessage a RIGHT JOIN tb_product_head b ON a.head_id=b.id WHERE a.d_code='{}' AND a.t_name='{}' ORDER BY a.id DESC limit 1",
    "get_count": "select me_no from tb_productionmessage WHERE t_name='{}' AND d_code='{}'",
    "get_ng_count": "select me_no from tb_productionmessage WHERE t_name='{}' AND d_code='{}' AND wheel_1!='' AND wheel_1 IS NOT NULL OR wheel_2!=''AND wheel_2 IS NOT NULL OR wheel_3!='' AND wheel_3 IS NOT NULL ",
    "get_type_id": "SELECT DISTINCT(b.id) from tb_designmessage a INNER JOIN tb_c_type_head b ON a.t_type=b.c_type WHERE a.t_name='{}'",
    "get_picture": "SELECT size_picture from tb_c_type_body WHERE c_type_id={} and size_code='{}'",
    "get_ip": "select ip_address from view_total WHERE new_name='{}'"
}
